<?php
// +----------------------------------------------------------------------
// | quanli team
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2020 http://www.fistforward.com/ All rights reserved.
// +----------------------------------------------------------------------
// | Author: allen.qiang
// +----------------------------------------------------------------------

/**
 * 区域管理类
 * @package  htdocs 
 * @subpackage  quanliadmin
 * @author   allen.qiang
 * @version 1.0
 */

class sysRegion{

    private $dbca;

    public function __construct( $dbca ) {
        $this->dbca = $dbca;
    }

    /**
     * 获取区域信息
     * @param $arg_array
     * @return array|null
     */
    public function getAreaManageList(&$arg_array){

        //sql字符串
        $sql = '';

        //参数类型字符串
        $params_type = '';

        //参数值数组
        $params_array = array();

        //区域ID
        if (isset($arg_array['id']) && is_numeric($arg_array['id'])) {
            $sql .= ' AND id = ?';
            $params_type .= 'i';
            $params_array[] = $arg_array['id'];
        }

        //区域名称
        if (isset($arg_array['name']) && !empty($arg_array['name'])) {
            $sql .= ' AND name LIKE ?';
            $params_type .= 's';
            $params_array[] = '%'.$arg_array['name'].'%';
        }

        //上级区域ID
        if (isset($arg_array['parentID']) && is_numeric($arg_array['parentID'])) {
            $sql .= ' AND parentID = ?';
            $params_type .= 'i';
            $params_array[] = $arg_array['parentID'];
        }

        //区域类型
        if (isset($arg_array['region_level']) && !empty($arg_array['region_level'])) {
            $sql .= ' AND region_level = ?';
            $params_type .= 'i';
            $params_array[] = $arg_array['region_level'];
        }

        if ($sql) {
            $sql = ' WHERE ' . substr($sql, 4) ;
        }



        try {

            if (isset($arg_array['rp'])) {

                //统计数量
                $search_sql = 'SELECT COUNT(*) AS item_count FROM sys_region  ' . $sql;
                $count_array = &$this->dbca->stmt_query($search_sql, $params_type, $params_array);

                $item_count = $count_array[0]->item_count;
                if ($item_count == 0) {
                    $arg_array['count'] = 0;
                    return null;
                }

                $arg_array['count'] = $item_count;

                //排序
                if (isset($arg_array['sortname']) && !empty($arg_array['sortname']))
                    $sql .= sprintf(' ORDER BY %s %s', $arg_array['sortname'], $arg_array['sortorder']);

                //分页
                if (!isset($arg_array['page']) || !is_numeric($arg_array['page']) || $arg_array['page'] <= 0)
                    $arg_array['page'] = 1;

                $sql .= sprintf(' LIMIT %d, %d', ($arg_array['page'] - 1) * $arg_array['rp'], $arg_array['rp']);

            } else {

                //排序
                if (isset($arg_array['sortname']) && !empty($arg_array['sortname']))
                    $sql .= sprintf(' ORDER BY %s %s', $arg_array['sortname'], $arg_array['sortorder']);

            }


            //查询备注信息
            $search_sql = 'SELECT * FROM sys_region ' . $sql;

            $temp_item_array = &$this->dbca->stmt_query($search_sql, $params_type, $params_array);

            if (!$temp_item_array)
                return null;

            if ($temp_item_array) {
                foreach ($temp_item_array as &$item) {

                    $search_son = $this->dbca->stmt_query('SELECT count(*) as num FROM sys_region WHERE parentID = ?','i',$item->id);

                    if($search_son[0]->num > 0){
                        $hasMid = 1;
                    }else{
                        $hasMid = 0;
                    }

                    $item_array[] = array(
                        'createTime' => $item->createTime,						//创建时间
                        'id' => $item->id,										//区域ID
                        'name' => $item->name,									//区域名称
                        'alias' => $item->alias,								//区域别名
                        'parentID' => $item->parentID,							//上级区域ID
                        'sortOrder' => $item->sortOrder,						//排序序号
                        'region_level' => $item->region_level,					//区域类型
                        'postCode' => $item->rpostCode,							//邮编
                        'hasMid' => $hasMid,
                        'front_display' => $item->front_display					//前端是否显示
                    );
                }
            }

            return $item_array;

        } catch (Exception $e) {

            error_log($e->getMessage());
            return null;

        }


    }

    /**
     * 获取市信息
     * @param $arg_array
     * @return array|null
     */
    public function searchMidRegion(&$arg_array){

        //sql字符串
        $sql = '';

        //参数类型字符串
        $params_type = '';

        //参数值数组
        $params_array = array();

        //分类ID
        if (isset($arg_array['id']) && is_numeric($arg_array['id'])) {
            $sql .= ' AND id = ?';
            $params_type .= 'i';
            $params_array[] = $arg_array['id'];
        }

        //上级区域ID
        if (isset($arg_array['parentID'])  && is_numeric($arg_array['parentID'])) {
            $sql .= ' AND parentID = ?';
            $params_type .= 'i';
            $params_array[] = $arg_array['parentID'];
        }

        if ($sql) {
            $sql = 'WHERE ' . substr($sql, 4);
        }

        try {

            if (isset($arg_array['rp']) && $arg_array['rp'] > 1) {

                //统计数量
                $search_sql = 'SELECT COUNT(*) AS item_count FROM sys_region ' . $sql;
                $count_array = &$this->dbca->stmt_query($search_sql, $params_type, $params_array);
                $item_count = $count_array[0]->item_count;
                if ($item_count == 0) {
                    $arg_array['count'] = 0;
                    return null;
                }

                $arg_array['count'] = $item_count;

                //排序
                if (isset($arg_array['sortname']) && !empty($arg_array['sortname']))
                    $sql .= sprintf(' ORDER BY %s %s', $arg_array['sortname'], $arg_array['sortorder']);

                //分页
                if (!isset($arg_array['page']) || !is_numeric($arg_array['page']) || $arg_array['page'] <= 0)
                    $arg_array['page'] = 1;

                $sql .= sprintf(' LIMIT %d, %d', ($arg_array['page'] - 1) * $arg_array['rp'], $arg_array['rp']);

            } else {

                //排序
                if (isset($arg_array['sortname']) && !empty($arg_array['sortname']))
                    $sql .= sprintf(' ORDER BY %s %s', $arg_array['sortname'], $arg_array['sortorder']);

            }

            //查询分类信息
            $search_sql = 'SELECT * FROM sys_region ' . $sql;
            $temp_item_array = &$this->dbca->stmt_query($search_sql, $params_type, $params_array);
            if (!$temp_item_array)
                return null;

            foreach($temp_item_array as &$item) {

                $smallNum = $this->dbca->stmt_query('select count(*) as num from sys_region where parentID = ? ','i',$item->parentID);

                $hasSma = $smallNum[0]->num?1:0;

                $item_array[] = array(
                    'createTime'  => $item->createTime,
                    'id'          =>  $item->id,
                    'name' => $item->name,
                    'parentID'       =>  $item->parentID,
                    'sortOrder'        =>  $item->sortOrder,
                    'region_level' => $item->region_level,
                    'postCode'  => $item->postCode,
                    'hasSma' => $hasSma
                );
            }

            return $item_array;

        } catch (Exception $e) {

            error_log($e->getMessage());
            return null;

        }

    }

    /**
     * 保存 编辑省信息
     * @param $arg_array
     * @return array|null
     */
    public function saveProvince(&$arg_array){

        if(!isset($arg_array['province'], $arg_array['sortOrder']) ){
            return array('code'=>400,'message'=>'参数错误');
        }

        try{

            $sys_region = new entity('sys_region');

            if(isset($arg_array['editStr']) && !empty($arg_array['editStr'])){
                $sys_region->id = $arg_array['editStr'];
                if(!$this->dbca->fetch($sys_region)){
                    return array('code'=>400,'message'=>'该区域不存在');
                }

                $sql = 'SELECT COUNT(*) AS num,id FROM sys_region WHERE region_level = 1 AND name = "'.$arg_array['province'] .'"';
                $count_arr = &$this->dbca->stmt_query($sql);
                if( $count_arr[0]->num  > 0 && $count_arr[0]->id !=$arg_array['editStr'] ){
                    return array('code'=>400,'message'=>'该省份已存在，请重新编辑');
                }

                $sys_region->name = $arg_array['province'];
                $sys_region->parentID = 0;
                $sys_region->sortOrder = $arg_array['sortOrder'];
                $sys_region->region_level = 1;
                $this->dbca->save($sys_region);

                $insert_id = getSortNum('sys_region');

                return array('code'=>200,'message'=>'保存成功','insert_id'=>$insert_id);
            }else{
                $sys_region = new entity('sys_region');
                $sys_region->name = $arg_array['province'];
                $sys_region->region_level = 1;

                if($this->dbca->fetch($sys_region)){
                    return array('code'=>400,'message'=>'该省份已存在，不能重复添加！');
                }
                //$sys_region->name = $arg_array['province'];
                $sys_region->parentID = 0;
                $sys_region->sortOrder = $arg_array['sortOrder'];
                //$sys_region->region_level = 1;
                $this->dbca->save($sys_region);

                $insert_id = getSortNum('sys_region');

                return array('code'=>200,'message'=>'保存成功','insert_id'=>$insert_id);
            }



        }catch (Exception $e){

            error_log($e->getMessage());
            return null;

        }


    }

    /**
     * 保存 编辑市信息
     * @param $arg_array
     * @return array|null
     */
    public function saveCity(&$arg_array){

        if(!isset($arg_array['m_provinceID'], $arg_array['m_sortOrder'], $arg_array['city'])){
            return array('code'=>400,'message'=>'参数错误');
        }

        try{

            $sys_region = new entity('sys_region');

            if(isset($arg_array['editStr']) && !empty($arg_array['editStr'])){
                $sys_region->id = $arg_array['editStr'];
                if(!$this->dbca->fetch($sys_region)){
                    return array('code'=>400,'message'=>'该区域不存在');
                }

                $sql = 'SELECT COUNT(*) AS num,id FROM sys_region WHERE region_level = 2 AND name = "'.$arg_array['city'] .'" AND parentID='.$arg_array['m_provinceID'];
                $count_arr = &$this->dbca->stmt_query($sql);
                if( $count_arr[0]->num  > 0 && $count_arr[0]->id !=$arg_array['editStr']){
                    return array('code'=>400,'message'=>'该市已存在，请重新编辑');
                }

                $sys_region->name = $arg_array['city'];
                $sys_region->parentID = $arg_array['m_provinceID'];
                $sys_region->sortOrder = $arg_array['m_sortOrder'];
                $sys_region->region_level = 2;

                $this->dbca->save($sys_region);

                $insert_id = getSortNum('sys_region');

                return array('code'=>200,'message'=>'保存成功','insert_id'=>$insert_id);
            }else{

                $sys_region = new entity('sys_region');
                $sys_region->name = $arg_array['city'];
                $sys_region->region_level = 2;
                $sys_region->parentID = $arg_array['m_provinceID'];
                if($this->dbca->fetch($sys_region)){
                    return array('code'=>400,'message'=>'该市已存在，不能重复添加！');
                }

                //$sys_region->name = $arg_array['city'];
                //$sys_region->parentID = $arg_array['m_provinceID'];
                $sys_region->sortOrder = $arg_array['m_sortOrder'];
                //$sys_region->region_level = 2;
                //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 start
                $sys_region->alias = $this->getAlias();
                //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 end
                $this->dbca->save($sys_region);
                //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 start
                $sys_seqno_name = 'seqno_bill_code' . $sys_region->alias;
                $sys_seqno_remark = $arg_array['city'] .'订单编号流水号';
                if (!$this->dbca->stmt_query('INSERT INTO sys_seqno (NAME,seqno,remark) VALUES ( ?,1000,?)','ss', $sys_seqno_name,$sys_seqno_remark)){
                    return array('code'=>400,'message'=>'sys_seqno处理失败');
                }
                //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 end

                $insert_id = getSortNum('sys_region');

                return array('code'=>200,'message'=>'保存成功','insert_id'=>$insert_id);
            }



        }catch(Exception $e){

            error_log($e->getMessage());
            return null;

        }

    }

    /**
     * 保存 编辑区信息
     * @param $arg_array
     * @return array|null
     */
    public function saveArea(&$arg_array){
        if(!isset($arg_array['s_provinceID'], $arg_array['s_cityID'], $arg_array['area'], $arg_array['s_sortOrder'])){
            return array('code'=>400,'message'=>'参数错误');
        }

        try{

            $sys_region = new entity('sys_region');

            if(isset($arg_array['editStr']) && !empty($arg_array['editStr'])){
                $sys_region->id = $arg_array['editStr'];
                if(!$this->dbca->fetch($sys_region)){
                    return array('code'=>400,'message'=>'该区域不存在');
                }

                $sql = 'SELECT COUNT(*) AS num ,id FROM sys_region WHERE region_level = 3 AND name = "'.$arg_array['area'] .'" AND parentID='.$arg_array['s_cityID'];

                $count_arr = &$this->dbca->stmt_query($sql);

                if( $count_arr[0]->num  > 0 && $count_arr[0]->id !=$arg_array['editStr'] ){
                    return array('code'=>400,'message'=>'该区已存在，请重新编辑');
                }else if( $count_arr[0]->num > 1)

                $sys_region->name = $arg_array['area'];
                $sys_region->parentID = $arg_array['s_cityID'];
                $sys_region->sortOrder = $arg_array['s_sortOrder'];
                $sys_region->region_level = 3;
                $this->dbca->save($sys_region);

                $insert_id = getSortNum('sys_region');

                return array('code'=>200,'message'=>'保存成功','insert_id'=>$insert_id);
            }else{

                $sys_region = new entity('sys_region');
                $sys_region->name = $arg_array['area'];
                $sys_region->region_level = 3;
                $sys_region->parentID = $arg_array['s_cityID'];
                if($this->dbca->fetch($sys_region)){
                    return array('code'=>400,'message'=>'该区已存在，不能重复添加！');
                }

                //$sys_region->name = $arg_array['area'];
                //$sys_region->parentID = $arg_array['s_cityID'];
                $sys_region->sortOrder = $arg_array['s_sortOrder'];
                //$sys_region->region_level = 3;
                $this->dbca->save($sys_region);

                $insert_id = getSortNum('sys_region');

                return array('code'=>200,'message'=>'保存成功','insert_id'=>$insert_id);
            }



        }catch(Exception $e){
            error_log($e->getMessage());
            return null;
        }


    }

    /**
     * 删除区域信息
     * @param $arg_array
     * @return array|null
     */
    public function deleteAreaManage(&$arg_array){

        if(!isset($arg_array['delete_str']) || !isset($arg_array['markType'])){
            return array('code'=>400,'message'=>'参数错误');
        }

        try{

            $b_arr = array();//删除数组ID
            //markType 为删除省 市 区 类型
            switch($arg_array['markType']){
                case 'b':
                    $b_arr[] = $arg_array['delete_str'];

                    //删除省
                    $this->dbca->stmt_query('DELETE FROM sys_region WHERE id = ?','i',$arg_array['delete_str']);

                    $m_region = $this->dbca->stmt_query('SELECT id,alias FROM sys_region where parentID = ?','i',$arg_array['delete_str']);

                    if($m_region){

                        foreach($m_region as $val){
                            $b_arr[] = $val->id;
                            //删除市
                           $this->dbca->stmt_query('DELETE FROM sys_region WHERE id = ?','i',$val->id);
                           //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 start
                           //去除城市取订单号的配置
                           $this->dbca->stmt_query('DELETE FROM sys_seqno WHERE NAME = ?','s','seqno_bill_code' . $val->alias );
                           //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 end

                            $s_region = $this->dbca->stmt_query('SELECT id FROM sys_region where parentID = ?','i',$val->id);

                            if($s_region){
                                foreach($s_region as $val1){
                                    $b_arr[] = $val1->id;
                                    //删除区
                                   $this->dbca->stmt_query('DELETE FROM sys_region WHERE id = ?','i',$val1->id);
                                }
                            }

                        }

                    }

                    break;

                case 'm':

                    $b_arr[] = $arg_array['delete_str'];

                    //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 start
                    //去除城市取订单号的配置
                    $s_region = $this->dbca->stmt_query('SELECT alias FROM sys_region where id = ?','i',$arg_array['delete_str']);
                    $this->dbca->stmt_query('DELETE FROM sys_seqno WHERE NAME = ?','s','seqno_bill_code' . $s_region[0]->alias );
                    //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 end

                    //删除市
                    $this->dbca->stmt_query('DELETE FROM sys_region WHERE id = ?','i',$arg_array['delete_str']);
                    $s_region = $this->dbca->stmt_query('SELECT id FROM sys_region where parentID = ?','i',$arg_array['delete_str']);

                    if($s_region){
                        foreach($s_region as $val1){
                            //删除区
                            $this->dbca->stmt_query('DELETE FROM sys_region WHERE id = ?','i',$val1->id);
                            $b_arr[] = $val1->id;
                        }
                    }

                    break;

                case 's':
                    $b_arr[] = $arg_array['delete_str'];
                    //删除区
                   $this->dbca->stmt_query('DELETE FROM sys_region WHERE id = ?','i',$arg_array['delete_str']);
                    break;

            }

            return array('code'=>200,'message'=>'删除成功','midArr'=>$b_arr);


        }catch(Exception $e){

            error_log($e->getMessage());
            return null;

        }

    }

    //add by allen.qiang 20150113 区域信息表中增加城市时对alias赋值 start
    //这个字段用于取订单编号用
    private function  getAlias(){
        $sql = "SELECT  MAX(alias)+1 AS retVal FROM sys_region";
        $ret_val = $this->dbca->stmt_query($sql);
        return $ret_val[0]->retVal;
    }

    //查询省市区
    public function searchRegion($level, $pid = null)
    {
        if(!$level || !in_array($level,array(1,2,3))) return null;

        $sql = "SELECT * FROM sys_region WHERE region_level = " . $level;
        if($pid) $sql .= ' AND parentID = '. $pid;
        return $this->dbca->stmt_query($sql);
    }
}